﻿using CIS.Web.Common;
using CIS.Web.Models;
using CIS.Web.Properties;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.IO;
using System.Linq;
using System.Linq.Dynamic;
using System.Threading.Tasks;
using System.Web.Mvc;
using static System.Net.Mime.MediaTypeNames;

namespace CIS.Web.Controllers
{
	[RoutePrefix("ConferenceStatistics")]
	public class ConferenceStatisticsController : BaseDbController
	{
		[Route("Index/{ConferenceId}")]
		public async Task<ActionResult> Index(string conferenceId)
		{
			string controller = RouteData.Values["controller"].ToString();
			string actionName = RouteData.Values["action"].ToString();
			Utility.InsertLog(LoginUser, Request.Url.ToString(), controller, actionName, "Index", conferenceId);

			var conferenceMaster = await DbContext.ConferenceMaster.AsNoTracking()
					.FirstOrDefaultAsync(x => x.Status == ConstValues.Status.正常 &&
						x.ID == conferenceId);

			ViewBag.ConferenceId = conferenceId;
			ViewBag.ConferenceName = conferenceMaster.ConferenceName;
			ViewBag.ControllerName = Resources.ConferenceStatistics;
			ViewBag.Language = conferenceMaster.IsInternational ? ConstValues.Language.En : ConstValues.Language.Cn;

			var model = new ConferenceStatisticsModel();
			model.ConferenceId = conferenceId;

			if (conferenceMaster.CheckinDate.Value.Year == conferenceMaster.CheckoutDate.Value.Year)
			{
				model.Days = conferenceMaster.CheckoutDate.Value.DayOfYear -
						conferenceMaster.CheckinDate.Value.DayOfYear + 1;
			}
			else
			{
				model.Days = conferenceMaster.CheckoutDate.Value.DayOfYear -
						conferenceMaster.CheckinDate.Value.DayOfYear + 366;
			}

			model.DayList = new List<string>();
			for (var index = 0; index < model.Days; index++)
			{
				string tmp = Request.Cookies["_culture"].Value == ConstValues.Language.Cn ?
							conferenceMaster.CheckinDate.Value.AddDays(index).ToString("yyyy-MM-dd") :
							conferenceMaster.CheckinDate.Value.AddDays(index).ToString("MM/dd/yyyy");
				model.DayList.Add(tmp);
			}

			LoginUser.ConferenceFunctionIdList = Utility.GetConferenceFunctionIdList(conferenceId);
			return View(model);
		}

		public async Task<JsonCamelCaseResult> GetContribute(int? page, int? rows, string sort, string order, ConferenceStatisticsModel model)
		{
			int take = rows ?? ConstValues.DefaultGridRows;
			int skip = ((page ?? 1) - 1) * take;

			var sql = DbContext.ViewConferenceContribute.AsNoTracking()
				.Where(x => x.ConferenceId == model.ConferenceId);

			if (!string.IsNullOrEmpty(model.TopicId))
			{
				sql = sql.Where(x => x.TopicId == model.TopicId);
			}
			if (!string.IsNullOrEmpty(model.JournalId))
			{
				sql = sql.Where(x => x.JournalId == model.JournalId);
			}

			int total = await sql.CountAsync();

			if (!string.IsNullOrEmpty(sort))
			{
				sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
			}
			else
			{
				sql = sql.OrderBy(x => x.TopicName).ThenBy(x => x.JournalName).Skip(skip).Take(take);
			}

			var sqlResult = await sql.ToListAsync();

			return JsonCamelCase(new
			{
				Total = total,
				Rows = sqlResult,
			},
			JsonRequestBehavior.AllowGet);
		}

		public async Task<JsonCamelCaseResult> GetAttendence(int? page, int? rows, string sort, string order, ConferenceStatisticsModel model)
		{
			int take = rows ?? ConstValues.DefaultGridRows;
			int skip = ((page ?? 1) - 1) * take;

			var conferenceMaster = await DbContext.ConferenceMaster.AsNoTracking()
					.FirstOrDefaultAsync(x => x.ID == model.ConferenceId);

			if (!string.IsNullOrEmpty(model.StatisticsGroupType))
			{
				if (model.StatisticsGroupType == "Country")
				{
					#region 国籍
					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupJoin(DbContext.LocationMaster.AsNoTracking(),
								info => info.Country,
								location => location.ID,
								(info, location) => new
								{
									Country = conferenceMaster.IsInternational ?
											location.FirstOrDefault().NameEn :
											location.FirstOrDefault().Name,
								})
							.GroupBy(x => new
							{
								x.Country
							})
							.Select(x => new
							{
								Country = x.Key.Country,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (sort == "country" || sort == "count")
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.Country).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
				else if (model.StatisticsGroupType == "Title")
				{
					#region 职务
					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupJoin(DbContext.TitleMaster.AsNoTracking(),
								info => info.Title,
								title => title.ID,
								(info, title) => new
								{
									Title = title.FirstOrDefault().TitleName,
								})
							.GroupBy(x => new
							{
								x.Title
							})
							.Select(x => new
							{
								Title = x.Key.Title,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (sort == "title" || sort == "count")
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.Title).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
				else if (model.StatisticsGroupType == "Job")
				{
					#region 职称
					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupBy(x => new
							{
								x.Job
							})
							.Select(x => new
							{
								Job = x.Key.Job,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (sort == "job" || sort == "count")
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.Job).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
				else if (model.StatisticsGroupType == "Affiliation")
				{
					#region 组织
					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupBy(x => new
							{
								x.Affiliation
							})
							.Select(x => new
							{
								Affiliation = x.Key.Affiliation,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (sort == "affiliation" || sort == "count")
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.Affiliation).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
				else if (model.StatisticsGroupType == "PayType")
				{
					#region 缴费方式
					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupJoin(DbContext.ConferencePaymentMaster.AsNoTracking(),
								info => info.PaymentTypeId,
								pay => pay.ID,
								(info, pay) => new
								{
									PaymentType = pay.FirstOrDefault().PayId,
								})
							.GroupJoin(DbContext.SysPaymentMaster.AsNoTracking(),
								pay => pay.PaymentType,
								type => type.ID,
								(pay, type) => new
								{
									PaymentName = type.FirstOrDefault().PaymentName,
								})
							.GroupBy(x => new
							{
								x.PaymentName
							})
							.Select(x => new
							{
								PaymentName = x.Key.PaymentName,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (sort == "paymentName" || sort == "count")
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.PaymentName).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult,
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
				else if (model.StatisticsGroupType == "PaidStatus")
				{
					#region 缴费状态
					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupBy(x => new
							{
								x.HasPaid
							})
							.Select(x => new
							{
								HasPaid = x.Key.HasPaid,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (sort == "hasPaid" || sort == "count")
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.HasPaid).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
				else if (model.StatisticsGroupType == "ArriveStatus")
				{
					#region 报到状态
					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupBy(x => new
							{
								x.HasArrived
							})
							.Select(x => new
							{
								HasArrived = x.Key.HasArrived,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (sort == "hasArrived" || sort == "count")
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.HasArrived).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
				else
				{
					#region 专题
					var topicList = await DbContext.TopicMaster.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.ToListAsync();

					var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
							.Where(x => x.ConferenceId == model.ConferenceId)
							.GroupBy(x => new
							{
								x.FavouriteTopicId
							})
							.Select(x => new
							{
								FavouriteTopicId = x.Key.FavouriteTopicId,
								Count = x.Count()
							});

					int total = await sql.CountAsync();

					if (!string.IsNullOrEmpty(sort))
					{
						sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
					}
					else
					{
						sql = sql.OrderBy(x => x.FavouriteTopicId).Skip(skip).Take(take);
					}

					var sqlResult = await sql.ToListAsync();

					return JsonCamelCase(new
					{
						Total = total,
						Rows = sqlResult
					},
					JsonRequestBehavior.AllowGet);
					#endregion
				}
			}
			else
			{
				var joins = from info in DbContext.ConferenceAttendenceInfo.AsNoTracking().Where(x => x.ConferenceId == model.ConferenceId)
					  join locationMaster in DbContext.LocationMaster.AsNoTracking()
						       on info.Country equals locationMaster.ID into t1
					  from lm in t1.DefaultIfEmpty()
					  join titleMaster in DbContext.TitleMaster.AsNoTracking().Where(x => x.Status == ConstValues.Status.正常)
						       on info.Title equals titleMaster.ID into t2
					  from tm in t2.DefaultIfEmpty()
					  join conferencePaymentMaster in DbContext.ConferencePaymentMaster.AsNoTracking().Where(x => x.Status == ConstValues.Status.正常)
						       on info.PaymentTypeId equals conferencePaymentMaster.ID into t3
					  from cp in t3.DefaultIfEmpty()
					  join sysPaymentMaster in DbContext.SysPaymentMaster.AsNoTracking().Where(x => x.Status == ConstValues.Status.正常)
						       on cp.PayId equals sysPaymentMaster.ID into t4
					  from sp in t4.DefaultIfEmpty()
					  select new
					  {
						  Country = conferenceMaster.IsInternational ? lm.NameEn : lm.Name,
						  Title = tm.TitleName,
						  Job = info.Job,
						  Affiliation = info.Affiliation,
						  PaymentName = conferenceMaster.IsInternational ? sp.PaymentNameEn : sp.PaymentName,
						  HasPaid = info.HasPaid,
						  HasArrived = info.HasArrived,
					  };

				var sql = joins.GroupBy(x => new
				{
					x.Country,
					x.Title,
					x.Job,
					x.Affiliation,
					x.PaymentName,
					x.HasPaid,
					x.HasArrived
				}).Select(x => new
				{
					Country = x.Key.Country,
					Title = x.Key.Title,
					Job = x.Key.Job,
					Affiliation = x.Key.Affiliation,
					PaymentName = x.Key.PaymentName,
					HasPaid = x.Key.HasPaid,
					HasArrived = x.Key.HasArrived,
					Count = x.Count()
				});

				int total = await sql.CountAsync();

				if (!string.IsNullOrEmpty(sort))
				{
					sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
				}
				else
				{
					sql = sql.OrderBy(x => x.Country).ThenBy(x => x.Title).ThenBy(x => x.Job).ThenBy(x => x.Affiliation).Skip(skip).Take(take);
				}

				var result = await sql.ToListAsync();

				return JsonCamelCase(new
				{
					Total = total,
					Rows = result
				},
				JsonRequestBehavior.AllowGet);
			}
		}

		public async Task<JsonCamelCaseResult> GetHotelRoom(int? page, int? rows, ConferenceStatisticsModel model)
		{
			DbContext.Database.Connection.Open();
			var command = DbContext.Database.Connection.CreateCommand();
			command.CommandType = System.Data.CommandType.StoredProcedure;
			command.CommandText = "ProcHotelRoom";
			command.CommandTimeout = 0;
			command.Parameters.Add(new System.Data.SqlClient.SqlParameter("@ConferenceId", model.ConferenceId));
			var result = new List<Dictionary<string, object>>();

			using (var reader = await command.ExecuteReaderAsync())
			{
				object[] values = new object[reader.FieldCount];
				while (await reader.ReadAsync())
				{
					reader.GetValues(values);

					if (!string.IsNullOrEmpty(model.HotelId) && model.HotelId != values[0].ToString())
					{
						continue;
					}

					Dictionary<string, object> item = new Dictionary<string, object>();
					item.Add("HotelName", values[1]);
					item.Add("RoomType", values[2]);

					for (int index = 0; index < model.Days; index++)
					{
						item.Add("Day" + index.ToString(), Utility.ObjectToInt(values[4 + index]));
					}
					result.Add(item);
				}
			}

			int take = rows ?? ConstValues.DefaultGridRows;
			int skip = ((page ?? 1) - 1) * take;
			var joins = result.Skip(skip).Take(take);

			return JsonCamelCase(new
			{
				Total = result.Count(),
				Rows = joins,
			},
			JsonRequestBehavior.AllowGet);
		}

		public async Task<JsonCamelCaseResult> GetInvoice(int? page, int? rows, string sort, string order, ConferenceStatisticsModel model)
		{
			int take = rows ?? ConstValues.DefaultGridRows;
			int skip = ((page ?? 1) - 1) * take;

			var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
					.Where(x => x.ConferenceId == model.ConferenceId &&
						x.HasInvoiced)
					.GroupBy(x => new
					{
						x.InvoiceTitle,
						x.TaxNum,
						x.ActuallyFee,
					})
					.Select(x => new
					{
						InvoiceTitle = x.Key.InvoiceTitle,
						TaxNum = x.Key.TaxNum,
						InvoiceAmount = x.Key.ActuallyFee,
						FeeCount = x.Count()
					});

			if (!string.IsNullOrEmpty(model.InvoiceTitle))
			{
				sql = sql.Where(x => x.InvoiceTitle.Contains(model.InvoiceTitle));
			}
			if (!string.IsNullOrEmpty(model.TaxNum))
			{
				sql = sql.Where(x => x.TaxNum.Contains(model.TaxNum));
			}

			int total = await sql.CountAsync();

			if (!string.IsNullOrEmpty(sort))
			{
				sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
			}
			else
			{
				sql = sql.OrderBy(x => x.InvoiceTitle).ThenBy(x => x.TaxNum).Skip(skip).Take(take);
			}

			var result = await sql.ToListAsync();

			return JsonCamelCase(new
			{
				Total = total,
				Rows = result,
			},
			JsonRequestBehavior.AllowGet);
		}

		public async Task<JsonCamelCaseResult> GetNotInvoice(int? page, int? rows, string sort, string order, ConferenceStatisticsModel model)
		{
			int take = rows ?? ConstValues.DefaultGridRows;
			int skip = ((page ?? 1) - 1) * take;

			var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
					.Where(x => x.ConferenceId == model.ConferenceId &&
						!x.HasInvoiced)
					.GroupBy(x => new
					{
						x.InvoiceTitle,
						x.TaxNum,
						x.ActuallyFee,
					})
					.Select(x => new
					{
						InvoiceTitle = x.Key.InvoiceTitle,
						TaxNum = x.Key.TaxNum,
						InvoiceAmount = x.Key.ActuallyFee,
						FeeCount = x.Count()
					});

			if (!string.IsNullOrEmpty(model.InvoiceTitle))
			{
				sql = sql.Where(x => x.InvoiceTitle.Contains(model.InvoiceTitle));
			}
			if (!string.IsNullOrEmpty(model.TaxNum))
			{
				sql = sql.Where(x => x.TaxNum.Contains(model.TaxNum));
			}

			int total = await sql.CountAsync();

			if (!string.IsNullOrEmpty(sort))
			{
				sql = sql.OrderBy(sort + ConstValues.chrSpace + order).Skip(skip).Take(take);
			}
			else
			{
				sql = sql.OrderBy(x => x.InvoiceTitle).ThenBy(x => x.TaxNum).Skip(skip).Take(take);
			}

			var result = await sql.ToListAsync();

			return JsonCamelCase(new
			{
				Total = total,
				Rows = result,
			},
			JsonRequestBehavior.AllowGet);
		}

		[HttpPost]
		[ValidateAntiForgeryToken]
		public async Task<FileResult> ExportInvoice(ConferenceStatisticsModel model)
		{
			string controller = RouteData.Values["controller"].ToString();
			string actionName = RouteData.Values["action"].ToString();
			Utility.InsertLog(LoginUser, Request.Url.ToString(), controller, actionName, "Index", model.HidConferenceId);

			var hasInvoiced = model.HidHasInvoiced == "true" ? true : false;

			var sql = DbContext.ConferenceAttendenceInfo.AsNoTracking()
				.Where(x => x.ConferenceId == model.HidConferenceId &&
					x.HasInvoiced == hasInvoiced)
				.GroupBy(x => new
				{
					x.InvoiceTitle,
					x.TaxNum,
					x.ActuallyFee,
				})
				.Select(x => new
				{
					InvoiceTitle = x.Key.InvoiceTitle,
					TaxNum = x.Key.TaxNum,
					InvoiceAmount = x.Key.ActuallyFee,
					FeeCount = x.Count()
				});

			if (!string.IsNullOrEmpty(model.HidInvoiceTitle))
			{
				sql = sql.Where(x => x.InvoiceTitle.Contains(model.HidInvoiceTitle));
			}
			if (!string.IsNullOrEmpty(model.HidTaxNum))
			{
				sql = sql.Where(x => x.TaxNum.Contains(model.HidTaxNum));
			}

			var result = await sql.ToListAsync();

			var export = Resources.export_statisticsInvoice;
			XSSFWorkbook workbook;

			using (MemoryStream readStream = new MemoryStream(export))
			{
				workbook = new XSSFWorkbook(readStream);
				ISheet sheet = workbook.GetSheetAt(0);

				var count = 0;				
				foreach (var x in result)
				{
					count += 1;
					IRow row = sheet.CreateRow(count);

					//发票抬头
					int column = 0;
					ICell cell = row.CreateCell(column);
					cell.SetCellValue(x.InvoiceTitle);

					//纳税人识别号
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(x.TaxNum);

					// 开票金额
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(x.InvoiceAmount.HasValue ? x.InvoiceAmount.Value.ToString() : "0" );

					// 数量
					column++;
					cell = row.CreateCell(column);
					cell.SetCellValue(x.FeeCount.ToString());
				}
			}

			MemoryStream exportStream = new MemoryStream();
			workbook.Write(exportStream);
			exportStream.Flush();
			exportStream.Position = 0;

			return File(exportStream, Application.Octet,
					$"export_contributeInfo_{DateTime.Now.ToString("yyyyMMddHHmmss")}.xlsx");
		}
	}
}